﻿/* VIEWS */
CREATE OR REPLACE VIEW RELATORIO_EMPRESA AS
	SELECT emp.idempresas, emp.nome Empresa, emp.cnpj, emp.telefone, emp.website, p.nome Produto, c.nome Cidade, e.nome Estado, s.status 
	FROM empresas AS emp
	INNER JOIN status AS s
	ON emp.fkidstatus = s.idstatus
	INNER JOIN pais AS p 
	ON emp.fkidpais = p.idpais
	INNER JOIN cidade AS c
	ON emp.fkidcidade = c.idcidade
	INNER JOIN estado AS e
	ON c.fkidestado = e.idestado
	ORDER BY emp.idempresas;

CREATE VIEW RELATORIO_PRODUTO as
	SELECT P.idprodutos, p.nome produto, p.descricao desc_produto, s.status 
	FROM produtos AS p
	INNER JOIN status AS s
	ON
	p.fkidstatus = s.idstatus
	order by p.idprodutos;

CREATE OR REPLACE VIEW RELATORIO_CAMPANHA AS
	SELECT c.idcampanhas, c.nome Campanha, c.descricao Campanha_desc, c.fkidstatus, o.idoportunidades, 
	       o.descricao Oportunidade_desc, p.nome Produto, e.nome Empresa
	FROM oportunidades AS o
	INNER JOIN campanhas AS c
	ON c.idcampanhas = o.fkidcampanhas
	INNER JOIN produtos AS p
	ON o.fkidprodutos = p.idprodutos 
	INNER JOIN empresas AS e
	ON o.fkidempresas = e.idempresas
	INNER JOIN status AS s
	ON c.fkidstatus = s.idstatus
	ORDER BY c.idcampanhas;	


CREATE VIEW vw_agendadecompromisso AS
	select idagendadecompromisso,titulo,data_compromisso,hora_inicio,hora_fim,descricao 
	from AgendadeCompromisso 
	where fkidstatus = 0
	order by data_compromisso;

CREATE VIEW vw_campanhas AS
	select idcampanhas, nome, descricao 
	from campanhas 
	where fkidstatus = 0 
	order by nome;

CREATE VIEW vw_cidade AS
	select c.idcidade, c.nome cidade, e.nome estado, p.nome pais 
	from (cidade as c inner join estado as e on c.fkidestado = e.idestado) 
	inner join pais as p 
	ON e.fkidpais = p.idpais 
	where c.fkidstatus = 0 
	order by e.nome;

CREATE VIEW vw_contatos AS
	select idcontatos, c.nome contato, emp.nome empresa, c.telefone, c.celular,c.email, p.nome pais, e.nome estado, cid.nome cidade 
	from contatos as c 
	inner join empresas as emp 
	on c.fkidempresas = emp.idempresas 
	inner join pais as p 
	on c.fkidpais = p.idpais 
	inner join estado as e 
	ON c.fkidestado = e.idestado 
	inner join cidade AS cid 
	ON c.fkidcidade = cid.idcidade 
	where c.fkidstatus = 0 
	order by p.nome;

CREATE VIEW vw_atividade AS
	select a.idatividade, emp.nome empresa, p.nome produto, a.descricao, a.dataretorno
	from atividade as a 
	inner join empresas as emp 
	on a.fkidempresas = emp.idempresas
	inner join usuarios as u
	on a.fkidusuarios = u.idusuarios
	inner join oportunidades as op
	on a.fkidoportunidades = op.idoportunidades
	inner join produtos as p
	on op.fkidprodutos = p.idprodutos
	where a.fkidstatus = 0 
	order by a.dataretorno desc;

		
CREATE OR REPLACE VIEW vw_atividade_historico AS
select a.idatividade as idatividade , a.fkidoportunidades as fkidoportunidades, a.fkidempresas as fkidempresas,
 emp.nome as empresa, a.descricao as descricao, a.dataretorno as dataretorno ,u.nome as responsavel, a.fkidusuarios as fkidusuarios
	from atividade as a 
	inner join empresas as emp 
	on a.fkidempresas = emp.idempresas
	inner join usuarios as u
	on a.fkidusuarios = u.idusuarios
	where a.fkidstatus = 0 
	order by a.dataretorno desc;


CREATE OR REPLACE VIEW vw_empresas AS
	select idempresas, emp.nome empresa, emp.telefone, p.nome pais, e.nome estado, c.nome cidade 
	from empresas as emp 
	inner join pais as p 
	on emp.fkidpais = p.idpais 
	inner join estado as e 
	ON emp.fkidestado = e.idestado 
	inner join cidade as c 
	ON emp.fkidcidade = c.idcidade
	where emp.fkidstatus =0;


CREATE VIEW vw_estado AS
	select idestado, e.nome estado, p.nome pais
	from estado as e 
	inner join pais as p 
	on e.fkidpais = p.idpais 
	where e.fkidstatus = 0 
	order by p.nome;

CREATE VIEW vw_fases AS
	select idfases, nome
	from fases 
	where fkidstatus = 0 
	order by nome;

CREATE VIEW vw_leads AS
	select idleads, nome, empresa, telefone 
	from leads 
	where fkidstatus = 0
	order by nome;

CREATE VIEW vw_oportunidades AS
	select idoportunidades, emp.nome empresa, p.nome produto, c.nome contato, f.nome fase, st.status situacao
	from oportunidades as o 
	inner join empresas as emp 
	on o.fkidempresas = emp.idempresas 
	inner join produtos as p 
	on o.fkidprodutos = p.idprodutos 
	inner join contatos as c 
	on o.fkidcontatos = c.idcontatos 
	inner join fases as f 
	on o.fkidfases = f.idfases 
	inner join status as st
	on o.fkidstatus = st.idstatus
	where o.fkidstatus = 0 
	order by emp.nome;


CREATE VIEW vw_origemleads AS
	select idorigemleads, nome
	from origemleads 
	where fkidstatus = 0 
	order by nome;

CREATE VIEW vw_pais AS
	select idpais, nome 
	from pais 
	where fkidstatus = 0 
	order by nome;

CREATE OR REPLACE VIEW vw_produtos AS
	SELECT * FROM produtos as p where p.fkidstatus = 0;

CREATE VIEW vw_status AS
	SELECT * FROM status;

CREATE VIEW vw_usuarios AS
	select idusuarios, nome 
	from usuarios 
	where fkidstatus = 0 
	order by nome;

CREATE  VIEW Historico AS
	SELECT idatividade, a.dataretorno, u.nome
	FROM atividade as a
	inner join usuarios as u
	on a.fkidusuarios = u.idusuarios;
	

CREATE VIEW vw_logcampanha AS
	SELECT * FROM 
	CAMPANHAS;

	
CREATE OR REPLACE VIEW vw_relatorio_campanha AS
	SELECT idlogcampanha, nome_novo, nome_antigo, descricao_novo, descricao_antigo,
	       fkidstatus_novo, fkidstatus_antigo
	FROM logcampanha;


/* Visao materializada */

CREATE TABLE matviews (
  mv_name NAME NOT NULL PRIMARY KEY
  , v_name NAME NOT NULL
  , last_refresh TIMESTAMP WITH TIME ZONE
);

CREATE OR REPLACE FUNCTION create_matview(NAME, NAME)
 RETURNS VOID
 SECURITY DEFINER
 LANGUAGE plpgsql AS '
 DECLARE
     matview ALIAS FOR $1;
     view_name ALIAS FOR $2;
     entry matviews%ROWTYPE;
 BEGIN
     SELECT * INTO entry FROM matviews WHERE mv_name = matview;
 
     IF FOUND THEN
         RAISE EXCEPTION ''Materialized view ''''%'''' already exists.'',
           matview;
     END IF;
 
     EXECUTE ''REVOKE ALL ON '' || view_name || '' FROM PUBLIC''; 
 
     EXECUTE ''GRANT SELECT ON '' || view_name || '' TO PUBLIC'';
 
     EXECUTE ''CREATE TABLE '' || matview || '' AS SELECT * FROM '' || view_name;
 
     EXECUTE ''REVOKE ALL ON '' || matview || '' FROM PUBLIC'';
 
     EXECUTE ''GRANT SELECT ON '' || matview || '' TO PUBLIC'';
 
     INSERT INTO matviews (mv_name, v_name, last_refresh)
       VALUES (matview, view_name, CURRENT_TIMESTAMP); 
     
     RETURN;
 END
 ';

SELECT create_matview('vw_mat_cidade', 'vw_cidade');
CREATE INDEX nome_idx ON vw_mat_cidade(nome);
	
